package com.bw.dao;

import com.bw.bean.Emp;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface EmpDao {

    @Select("SELECT\n" +
            "    base_category2.`name` AS `name`, \n" +
            "    sum(order_info.final_total_amount) AS `value`\n" +
            "FROM\n" +
            "    order_info\n" +
            "    INNER JOIN\n" +
            "    order_detail\n" +
            "    ON \n" +
            "        order_info.id = order_detail.order_id\n" +
            "    INNER JOIN\n" +
            "    sku_info\n" +
            "    ON \n" +
            "        order_detail.sku_id = sku_info.id\n" +
            "    INNER JOIN\n" +
            "    base_category3\n" +
            "    ON \n" +
            "        sku_info.category3_id = base_category3.id\n" +
            "    INNER JOIN\n" +
            "    base_category2\n" +
            "    ON \n" +
            "        base_category3.category2_id = base_category2.id\n" +
            "        \n" +
            "    GROUP BY base_category2.`name` order by value desc")
    List<Emp> getList1();

    @Select("select b.name,sum(final_total_amount) value \n" +
            "from order_info o\n" +
            "LEFT JOIN order_detail d on d.order_id=o.id\n" +
            "LEFT JOIN sku_info s ON d.sku_id=s.id\n" +
            "LEFT JOIN base_trademark b on s.tm_id=b.id\n" +
            "group by b.name")
    List<Emp> getList2();

    @Select("call zum()")
    Double getList3();

    @Select(" SELECT\n" +
            "    spu_info.spu_name AS `name`, \n" +
            "    sum(order_info.final_total_amount) AS `value`\n" +
            "FROM\n" +
            "    order_info\n" +
            "    INNER JOIN\n" +
            "    order_detail\n" +
            "    ON \n" +
            "        order_info.id = order_detail.order_id\n" +
            "    INNER JOIN\n" +
            "    sku_info\n" +
            "    ON \n" +
            "        order_detail.sku_id = sku_info.id\n" +
            "    INNER JOIN\n" +
            "    spu_info\n" +
            "    ON \n" +
            "        sku_info.spu_id = spu_info.id\n" +
            "    GROUP BY spu_info.spu_name")
    List<Emp> getList4();
}
